package com.jg.dao.impl;

import com.jg.dao.BaseDao;
import com.jg.dao.EmployeeProfilesDao;
import com.jg.entity.EmployeeProfiles;
import com.jg.util.Page;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 86130
 * @version 1.0
 * @description: TODO
 * @date 2025/3/27 10:31
 */
public class EmployeeProfilesDaoImpl extends BaseDao implements EmployeeProfilesDao {

    @Override
    public Page<EmployeeProfiles> findEmployeeProfilesAll(Page<EmployeeProfiles> page) {
        List<Object> params = new ArrayList<>();
        List<EmployeeProfiles> list = new ArrayList<>();
        String sql = "select * from employee_profiles limit ?,?";

        params.add((page.getCurrentPage() - 1) * page.getPageSize());
        params.add(page.getPageSize());
        ResultSet rs = executeQuery(sql,params.toArray());
        if (rs != null) {
            try {
                while (rs.next()) {
                    EmployeeProfiles employeeProfiles = new EmployeeProfiles();
                    employeeProfiles.setId(rs.getInt("id"));
                    employeeProfiles.setUserId(rs.getInt("user_id"));
                    employeeProfiles.setName(rs.getString("name"));
                    employeeProfiles.setPosition(rs.getString("position"));
                    employeeProfiles.setDepartment(rs.getInt("department"));
                    employeeProfiles.setHireDate(rs.getDate("hire_date"));
                    employeeProfiles.setLeaveDate(rs.getDate("leave_date"));
                    employeeProfiles.setStatus(rs.getString("status"));
                    list.add(employeeProfiles);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        page.setData(list);
        page.setTotalCount(totalCount());

        return page;
    }

    private int totalCount() {
        int totalCount = 0;
        List<Object> params = new ArrayList<>();
        String sql = "select count(*) count from employee_profiles";
        ResultSet rs = executeQuery(sql, params.toArray());
        if (null != rs) {
            try {
                while (rs.next()) {
                    totalCount = rs.getInt("count");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return totalCount;
    }

    @Override
    public EmployeeProfiles findEmployeeProfilesById(Integer id) {
        String sql = "select * from employee_profiles where id = ?";
        ResultSet rs = executeQuery(sql, id);
        EmployeeProfiles employeeProfiles = null;
        if (rs != null) {
            try {
                while (rs.next()) {
                    employeeProfiles = new EmployeeProfiles();
                    employeeProfiles.setId(rs.getInt("id"));
                    employeeProfiles.setUserId(rs.getInt("user_id"));
                    employeeProfiles.setName(rs.getString("name"));
                    employeeProfiles.setPosition(rs.getString("position"));
                    employeeProfiles.setDepartment(rs.getInt("department"));
                    employeeProfiles.setHireDate(rs.getDate("hire_date"));
                    employeeProfiles.setLeaveDate(rs.getDate("leave_date"));
                    employeeProfiles.setStatus(rs.getString("status"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return employeeProfiles;
    }


    @Override
    public Page<EmployeeProfiles> getEmployeeList(Page<EmployeeProfiles> page, String empName) {
        List<EmployeeProfiles> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT e.*,d.dept_name deptName FROM employee_profiles e,department d ");
        sql.append(" WHERE e.department = d.id ");
        if (empName != null && !empName.isEmpty()) {
            sql.append(" AND e.name LIKE CONCAT( '%', " + empName + ", '%' ) ");
        }
        sql.append(" ORDER BY e.id desc");
        sql.append(" limit " + (page.getCurrentPage() - 1) * page.getPageSize() + "," + page.getPageSize());
        ResultSet rs = executeQuery(sql.toString());
        try {
            while (rs.next()) {
                EmployeeProfiles employee = new EmployeeProfiles();
                employee.setId(rs.getInt("id"));
                employee.setUserId(rs.getInt("user_id"));
                employee.setName(rs.getString("name"));
                employee.setPosition(rs.getString("position"));
                employee.setDepartment(rs.getInt("department"));
                employee.setDeptName(rs.getString("deptName"));
                employee.setHireDate(rs.getDate("hire_date"));
                employee.setLeaveDate(rs.getDate("leave_date"));
                employee.setStatus(rs.getString("status"));
                list.add(employee);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closedAll(rs);
        }
        page.setData(list);
        page.setTotalCount(getEmployeeCount(empName));
        return page;
    }

    @Override
    public int getEmployeeCount(String empName) {
        int totalCount = 0;
        String sql = "select count(*) count from employee_profiles";
        if (empName != null && !empName.isEmpty()) {
            sql += " where name like concat('%'," + empName + ",'%')";
        }
        ResultSet rs = executeQuery(sql);
        if (null != rs) {
            try {
                while (rs.next()) {
                    totalCount = rs.getInt("count");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return totalCount;
    }

    @Override
    public int addEmployee(EmployeeProfiles employee) {
        String sql = "insert into employee_profiles(name,department,position,hire_date,leave_date,status) values(?,?,?,?,?,?)";
        return executeUpdate(sql,
                employee.getName(),
                employee.getDepartment(),
                employee.getPosition(),
                employee.getHireDate(),
                employee.getLeaveDate(),
                employee.getStatus());
    }

    @Override
    public int edit(EmployeeProfiles employee) {
        String sql = "update employee_profiles set name=?,department=?,position=?,hire_date=?,leave_date=?,status=? where id=?";
        return executeUpdate(sql,
                employee.getName(),
                employee.getDepartment(),
                employee.getPosition(),
                employee.getHireDate(),
                employee.getLeaveDate(),
                employee.getStatus(),
                employee.getId());
    }

    @Override
    public int deleteEmployee(int id) {
        String sql = "delete from employee_profiles where id=?";
        return executeUpdate(sql, id);
    }

    @Override
    public EmployeeProfiles getEmployeeById(int id) {
        String sql = "select * from employee_profiles where id=?";
        ResultSet rs = executeQuery(sql, id);
        EmployeeProfiles employee = null;
        if (rs != null){
            try {
                while (rs.next()){
                    employee = new EmployeeProfiles();
                    employee.setId(rs.getInt("id"));
                    employee.setUserId(rs.getInt("user_id"));
                    employee.setName(rs.getString("name"));
                    employee.setPosition(rs.getString("position"));
                    employee.setDepartment(rs.getInt("department"));
                    employee.setDeptName(rs.getString("deptName"));
                    employee.setHireDate(rs.getDate("hire_date"));
                    employee.setLeaveDate(rs.getDate("leave_date"));
                    employee.setStatus(rs.getString("status"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return employee;
    }
}
